# 数据库配置
import pymysql,json
from DBUtils.PooledDB import PooledDB
from pymysql.converters import escape_string

# 连接本项目数据库
mysqlInfo = {
    "host": 'rm-j6c9k122p6d2r4lh6.mysql.rds.aliyuncs.com', # rm-j6c9k122p6d2r4lh63o.mysql.rds.aliyuncs.com  rm-j6c9k122p6d2r4lh6.mysql.rds.aliyuncs.com
    "user": 'hk1root',
    "passwd": 'qA0130qwe@',
    "db": '',
    "port": 3306,
    "charset": 'utf8'
}


class ConnMysql(object):
    __pool = None

    def __init__(self,database):
        # 构造函数，创建数据库连接、游标
        self.coon = ConnMysql.getmysqlconn(database)
        self.cur = self.coon.cursor(cursor=pymysql.cursors.DictCursor)

    # 数据库连接池连接
    @staticmethod
    def getmysqlconn(database):
        global __pool
        if ConnMysql.__pool is None:
            __pool = PooledDB(
                creator=pymysql,
                mincached=1,
                maxcached=5,
                maxconnections=6,
                maxshared=3,
                blocking=True,
                maxusage=None,
                setsession=[],
                ping=2,
                host=database['host'],
                user=database['user'],
                passwd=database['passwd'],
                db=database['db'],
                port=database['port'],
                charset=database['charset'])
        return __pool.connection()

    def query(self,sql,param=None):
        """
        查询数据库
        :param sql: 查询SQL语句
        :param param: 参数
        :return: 返回集合
        """
        self.cur.execute(sql,param)
        result=self.cur.fetchall()
        return result

    # 插入、修改、删除一条
    def sql_change_msg(self, sql):
        change_sql = self.cur.execute(sql)
        self.coon.commit()
        return change_sql

    # 查询一条
    def sql_select_one(self, sql):
        self.cur.execute(sql)
        select_res = self.cur.fetchone()
        return select_res

    def count(self,sql,param=None):
        """
        统计当前表记录行数
        :param sql: 统计SQL语句
        :param param: 参数
        :return: 当前记录行
        """
        ret=self.sql_select_one(sql)
        count=None
        if ret:
            for k,v in ret.items():
                count=v
        return count

    def listByPage(self,sql,current_page,page_size,param=None):
        """
        分页查询当前表格数据
        :param sql: 查询SQL语句
        :param current_page: 当前页码
        :param page_size: 页码大小
        :param param:参数
        :return:
        """
        countSQL=f"select count(*) ct from ({sql}) tmp "
        countNum=self.count(countSQL,param)
        offset=(current_page-1)*page_size
        totalPage=int(countNum/page_size)
        if countNum % page_size>0:
            totalPage = totalPage + 1
        pagination={"current_page":current_page,"page_size":page_size,"count":countNum,"total_page":totalPage}
        querySql=f"select * from ({sql}) tmp limit"+" %s,%s"
        # 判断是否有参数
        if param==None:
            # 无参数
            querySql=f"select * from ({sql}) tmp limit {offset},{page_size}"
            pagination["data"]=self.query(querySql)
        else:
            # 有参数的情况,此时需要判断参数是元祖还是字典
            if isinstance(param,dict):
                # 字典的情况,因此需要添加字典
                querySql=f"select * from ({sql}) tmp limit"+" %(tmp_offset)s,%(tmp_pageSize)s"                
                param["tmp_offset"]=offset
                param["tmp_pageSize"]=page_size
                pagination["data"]=self.query(querySql,param)
            elif isinstance(param,tuple):
                # 元祖的方式
                listtp=list(param)
                listtp.append(offset)
                listtp.append(page_size)
                pagination["data"]=self.query(querySql,tuple(listtp))
            else:
                # 基础类型
                listtp=[]
                listtp.append(param)
                listtp.append(offset)
                listtp.append(page_size)
                pagination["data"]=self.query(querySql,tuple(listtp))
        return pagination

    # 查询多条
    def sql_select_many(self, sql, count=None):
        self.cur.execute(sql)
        if count is None:
            select_res = self.cur.fetchall()
        else:
            select_res = self.cur.fetchmany(count)
        return select_res

    # obj转sql语句
    def obj_to_sql(self,tablename:str,obj:dict,where:str=''):
        """
            update {tablename} set {sql} {where}
            insert into {tablename} set {sql}
            return {
                "update":update_sql,
                "insert": insert_sql
            }
        """
        sql = ""
        for key,value in obj.items():
            # 判断value数据类型是否为dict
            if isinstance(value,dict):
                value = json.dumps(value)
            # 判断value数据类型是否为int类型
            if isinstance(value,int):
                value = str(value)
            sql += f"`{key}`='{escape_string(value)}',"
        sql = sql[:-1]

        update_sql = f"update {tablename} set {sql} {where}"
        insert_sql = f"insert into {tablename} set {sql}"
        return {
            "update":update_sql,
            "insert": insert_sql
        }
        
    # 释放资源
    def release(self):
        self.coon.close()
        self.cur.close()


if __name__ == '__main__':
    db = ConnMysql()   # 查询站群数据库
    sql = "SHOW DATABASES"
    res = db.sql_select_many(sql)
    db.release() # 释放资源
    print(res)